﻿using System;
using System.Collections.Generic;
using System.Text;
using Wjtour.Common;
using System.Linq;
using System.Data.Common;
using System.Data;
using Hjp.FrameWork.Common;

namespace Wjtour.BLL
{
    public class member_bll : base_dll
    {
        public PaginationData<Wjtour.Domain.member> GetList(string sel, string q, string se2, string linput, string hinput, int page, int pagesize = 20)
        {
            string where = " where 1=1";
            if (sel == "username" && !string.IsNullOrWhiteSpace(q))
                where += " and username like '%" + Utility.GetSafeSql(q) + "'";
            if (sel == "id" && !string.IsNullOrWhiteSpace(q))
                where += " and id like '%" + Utility.GetSafeSql(q) + "'";
            if (sel == "qq" && !string.IsNullOrWhiteSpace(q))
                where += " and qq like '%" + Utility.GetSafeSql(q) + "'";
            if (sel == "email" && !string.IsNullOrWhiteSpace(q))
                where += " and email like '%" + Utility.GetSafeSql(q) + "'";
            if (sel == "mobile" && !string.IsNullOrWhiteSpace(q))
                where += " and mobile like '%" + Utility.GetSafeSql(q) + "'";
            if (sel == "realname" && !string.IsNullOrWhiteSpace(q))
                where += " and realname like '%" + Utility.GetSafeSql(q) + "'";

            double d1 = Utils.StrToDouble(linput, 0.0);
            double d2 = Utils.StrToDouble(hinput, 0.0);

            if (se2 == "loginnum" && d1 > 0 && d2 > 0)
            {
                where += " and loginnum >= " + d1;
                where += " and loginnum <= " + d2;
            }
            if (se2 == "fansnum" && d1 > 0 && d2 > 0)
            {
                where += " and fansnum >= " + d1;
                where += " and fansnum <= " + d2;
            }

            string sql = string.Format(@"select * from member
                {0} 
              order by id desc", where);
            PaginationData<Wjtour.Domain.member> data = GetPaginationData<Wjtour.Domain.member>(sql, page, pagesize);
            return data;
        }

        public Wjtour.Domain.member Load(int id)
        {
            string sql = "select * from member where id=" + id;
            return DbAccess.GetObjects<Wjtour.Domain.member>(sql).FirstOrDefault();
        }

        public Wjtour.Domain.member GetMemberinfo(int id)
        {
            string sql = "select * from member where id=" + id;
            return DbAccess.GetObjects<Wjtour.Domain.member>(sql).FirstOrDefault();
        }

        /// <summary>
        /// 根据账号获取会员实体信息
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public Wjtour.Domain.member GetMember(string username)
        {
            string sql = "select * from member where username='" + username + "'";
            return DbAccess.GetObjects<Wjtour.Domain.member>(sql).FirstOrDefault();
        }

        public Wjtour.Domain.member CheckMobile(string mobile)
        {
            string sql = "select * from member where mobile=@mobile";
            DbCommand cmd = DbAccess.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            DbAccess.AddInParameter(cmd, "@mobile", System.Data.DbType.String, mobile);

            return DbAccess.GetObjects<Wjtour.Domain.member>(cmd).FirstOrDefault();
        }

        public Wjtour.Domain.member CheckEmail(string email)
        {
            string sql = "select * from member where email=@email";
            DbCommand cmd = DbAccess.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            DbAccess.AddInParameter(cmd, "@email", System.Data.DbType.String, email);

            return DbAccess.GetObjects<Wjtour.Domain.member>(cmd).FirstOrDefault();
        }

        public Wjtour.Domain.member Login(string email, string password)
        {
            string sql = "select * from member where (email=@email or mobile=@email) and pwd=@pwd";
            DbCommand cmd = DbAccess.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            DbAccess.AddInParameter(cmd, "@email", System.Data.DbType.String, email);
            DbAccess.AddInParameter(cmd, "@pwd", System.Data.DbType.String, password);

            return DbAccess.GetObjects<Wjtour.Domain.member>(cmd).FirstOrDefault();
        }

        public bool Save(Wjtour.Domain.member entity)
        {
            string sql = "";

            DbCommand cmd = DbAccess.CreateCommand();

            if (entity.id > 0)
            {
                sql = @"UPDATE [member] SET 
                [username] = @username,
                [pwd] = @pwd,
                [realname] = @realname,
                [regtime] = @regtime,
                [regip] = @regip,
                [loginnum] = @loginnum,
                [lastlogintime] = @lastlogintime,
                [email] = @email,
                [jifen] = @jifen,
                [statuz] = @statuz,
                [level] = @level,
                [mobile] = @mobile,
                [mobile_test] = @mobile_test,
                [qq] = @qq,
                [pass_question] = @pass_question,
                [pass_answer] = @pass_answer,
                [jihuo] = @jihuo,
                [hart] = @hart,
                [photo] = @photo,
                [fansnum] = @fansnum,
                [friendsnum] = @friendsnum,
                [focusnum] = @focusnum,
                [weibo] = @weibo,
                [taobao] = @taobao,
                [tencent] = @tencent,
                [gender] = @gender,
                [province] = @province,
                [city] = @city,
                [birthday_year] = @birthday_year,
                [birthday_month] = @birthday_month,
                [birthday_day] = @birthday_day,
                [duty] = @duty,
                [label] = @label,
                [introduce] = @introduce,
                [address] = @address,
                [tel] = @tel
                 WHERE id=@id";
                DbAccess.AddInParameter(cmd, "@id", DbType.Int32, entity.id);

            }
            else
            {
                sql = @"INSERT INTO [member]( 
                [username],[pwd],[realname],[regtime],[regip],[loginnum],[lastlogintime],[email],[jifen],[statuz],[level],[mobile],[mobile_test],[qq],[pass_question],[pass_answer],[jihuo],[hart],[photo],[fansnum],[friendsnum],[focusnum],[weibo],[taobao],[tencent],[gender],[province],[city],[birthday_year],[birthday_month],[birthday_day],[duty],[label],[introduce],[address],[tel])VALUES(
                @username,@pwd,@realname,@regtime,@regip,@loginnum,@lastlogintime,@email,@jifen,@statuz,@level,@mobile,@mobile_test,@qq,@pass_question,@pass_answer,@jihuo,@hart,@photo,@fansnum,@friendsnum,@focusnum,@weibo,@taobao,@tencent,@gender,@province,@city,@birthday_year,@birthday_month,@birthday_day,@duty,@label,@introduce,@address,@tel);
                ";
                DbAccess.AddOutParameter(cmd, DbType.Int32, "@id");
            }
            DbAccess.AddInParameter(cmd, "@username", DbType.String, entity.username);
            DbAccess.AddInParameter(cmd, "@pwd", DbType.String, entity.pwd);
            DbAccess.AddInParameter(cmd, "@realname", DbType.String, entity.realname);
            DbAccess.AddInParameter(cmd, "@regtime", DbType.DateTime, entity.regtime);
            DbAccess.AddInParameter(cmd, "@regip", DbType.String, entity.regip);
            DbAccess.AddInParameter(cmd, "@loginnum", DbType.Int32, entity.loginnum);
            DbAccess.AddInParameter(cmd, "@lastlogintime", DbType.DateTime, entity.lastlogintime);
            DbAccess.AddInParameter(cmd, "@email", DbType.String, entity.email);
            DbAccess.AddInParameter(cmd, "@jifen", DbType.Int32, entity.jifen);
            DbAccess.AddInParameter(cmd, "@statuz", DbType.Int16, entity.statuz);
            DbAccess.AddInParameter(cmd, "@level", DbType.Int32, entity.level);
            DbAccess.AddInParameter(cmd, "@mobile", DbType.String, entity.mobile);
            DbAccess.AddInParameter(cmd, "@mobile_test", DbType.Int16, entity.mobile_test);
            DbAccess.AddInParameter(cmd, "@qq", DbType.String, entity.qq);
            DbAccess.AddInParameter(cmd, "@pass_question", DbType.String, entity.pass_question);
            DbAccess.AddInParameter(cmd, "@pass_answer", DbType.String, entity.pass_answer);
            DbAccess.AddInParameter(cmd, "@jihuo", DbType.Int16, entity.jihuo);
            DbAccess.AddInParameter(cmd, "@hart", DbType.Int32, entity.hart);
            DbAccess.AddInParameter(cmd, "@photo", DbType.String, entity.photo);
            DbAccess.AddInParameter(cmd, "@fansnum", DbType.Int32, entity.fansnum);
            DbAccess.AddInParameter(cmd, "@friendsnum", DbType.Int32, entity.friendsnum);
            DbAccess.AddInParameter(cmd, "@focusnum", DbType.Int32, entity.focusnum);
            DbAccess.AddInParameter(cmd, "@weibo", DbType.String, entity.weibo);
            DbAccess.AddInParameter(cmd, "@taobao", DbType.String, entity.taobao);
            DbAccess.AddInParameter(cmd, "@tencent", DbType.String, entity.tencent);
            DbAccess.AddInParameter(cmd, "@gender", DbType.String, entity.gender);
            DbAccess.AddInParameter(cmd, "@province", DbType.String, entity.province);
            DbAccess.AddInParameter(cmd, "@city", DbType.String, entity.city);
            DbAccess.AddInParameter(cmd, "@birthday_year", DbType.Int32, entity.birthday_year);
            DbAccess.AddInParameter(cmd, "@birthday_month", DbType.Int32, entity.birthday_month);
            DbAccess.AddInParameter(cmd, "@birthday_day", DbType.Int32, entity.birthday_day);
            DbAccess.AddInParameter(cmd, "@duty", DbType.Int32, entity.duty);
            DbAccess.AddInParameter(cmd, "@label", DbType.String, entity.label);
            DbAccess.AddInParameter(cmd, "@introduce", DbType.String, entity.introduce);
            DbAccess.AddInParameter(cmd, "@address", DbType.String, entity.address);
            DbAccess.AddInParameter(cmd, "@tel", DbType.String, entity.tel);
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;

            bool ret = DbAccess.ExecuteNonQuery(cmd) > 0;
            return ret;
        }


        public bool Save(Wjtour.Domain.member entity, out int newid)
        {
            string sql = "";

            DbCommand cmd = DbAccess.CreateCommand();

            if (entity.id > 0)
            {
                sql = @"UPDATE [member] SET 
                [username] = @username,
                [pwd] = @pwd,
                [realname] = @realname,
                [regtime] = @regtime,
                [regip] = @regip,
                [loginnum] = @loginnum,
                [lastlogintime] = @lastlogintime,
                [email] = @email,
                [jifen] = @jifen,
                [statuz] = @statuz,
                [level] = @level,
                [mobile] = @mobile,
                [mobile_test] = @mobile_test,
                [qq] = @qq,
                [pass_question] = @pass_question,
                [pass_answer] = @pass_answer,
                [jihuo] = @jihuo,
                [hart] = @hart,
                [photo] = @photo,
                [fansnum] = @fansnum,
                [friendsnum] = @friendsnum,
                [focusnum] = @focusnum,
                [weibo] = @weibo,
                [taobao] = @taobao,
                [tencent] = @tencent,
                [gender] = @gender,
                [province] = @province,
                [city] = @city,
                [birthday_year] = @birthday_year,
                [birthday_month] = @birthday_month,
                [birthday_day] = @birthday_day,
                [duty] = @duty,
                [label] = @label,
                [introduce] = @introduce,
                [address] = @address,
                [tel] = @tel
                 WHERE id=@id";
                DbAccess.AddInParameter(cmd, "@id", DbType.Int32, entity.id);

            }
            else
            {
                sql = @"INSERT INTO [member]( 
                [username],[pwd],[realname],[regtime],[regip],[loginnum],[lastlogintime],[email],[jifen],[statuz],[level],[mobile],[mobile_test],[qq],[pass_question],[pass_answer],[jihuo],[hart],[photo],[fansnum],[friendsnum],[focusnum],[weibo],[taobao],[tencent],[gender],[province],[city],[birthday_year],[birthday_month],[birthday_day],[duty],[label],[introduce],[address],[tel])VALUES(
                @username,@pwd,@realname,@regtime,@regip,@loginnum,@lastlogintime,@email,@jifen,@statuz,@level,@mobile,@mobile_test,@qq,@pass_question,@pass_answer,@jihuo,@hart,@photo,@fansnum,@friendsnum,@focusnum,@weibo,@taobao,@tencent,@gender,@province,@city,@birthday_year,@birthday_month,@birthday_day,@duty,@label,@introduce,@address,@tel);
                 select @id = @@identity
                ";
                DbAccess.AddOutParameter(cmd, DbType.Int32, "@id");
            }
            DbAccess.AddInParameter(cmd, "@username", DbType.String, entity.username);
            DbAccess.AddInParameter(cmd, "@pwd", DbType.String, entity.pwd);
            DbAccess.AddInParameter(cmd, "@realname", DbType.String, entity.realname);
            DbAccess.AddInParameter(cmd, "@regtime", DbType.DateTime, entity.regtime);
            DbAccess.AddInParameter(cmd, "@regip", DbType.String, entity.regip);
            DbAccess.AddInParameter(cmd, "@loginnum", DbType.Int32, entity.loginnum);
            DbAccess.AddInParameter(cmd, "@lastlogintime", DbType.DateTime, entity.lastlogintime);
            DbAccess.AddInParameter(cmd, "@email", DbType.String, entity.email);
            DbAccess.AddInParameter(cmd, "@jifen", DbType.Int32, entity.jifen);
            DbAccess.AddInParameter(cmd, "@statuz", DbType.Int16, entity.statuz);
            DbAccess.AddInParameter(cmd, "@level", DbType.Int32, entity.level);
            DbAccess.AddInParameter(cmd, "@mobile", DbType.String, entity.mobile);
            DbAccess.AddInParameter(cmd, "@mobile_test", DbType.Int16, entity.mobile_test);
            DbAccess.AddInParameter(cmd, "@qq", DbType.String, entity.qq);
            DbAccess.AddInParameter(cmd, "@pass_question", DbType.String, entity.pass_question);
            DbAccess.AddInParameter(cmd, "@pass_answer", DbType.String, entity.pass_answer);
            DbAccess.AddInParameter(cmd, "@jihuo", DbType.Int16, entity.jihuo);
            DbAccess.AddInParameter(cmd, "@hart", DbType.Int32, entity.hart);
            DbAccess.AddInParameter(cmd, "@photo", DbType.String, entity.photo);
            DbAccess.AddInParameter(cmd, "@fansnum", DbType.Int32, entity.fansnum);
            DbAccess.AddInParameter(cmd, "@friendsnum", DbType.Int32, entity.friendsnum);
            DbAccess.AddInParameter(cmd, "@focusnum", DbType.Int32, entity.focusnum);
            DbAccess.AddInParameter(cmd, "@weibo", DbType.String, entity.weibo);
            DbAccess.AddInParameter(cmd, "@taobao", DbType.String, entity.taobao);
            DbAccess.AddInParameter(cmd, "@tencent", DbType.String, entity.tencent);
            DbAccess.AddInParameter(cmd, "@gender", DbType.String, entity.gender);
            DbAccess.AddInParameter(cmd, "@province", DbType.String, entity.province);
            DbAccess.AddInParameter(cmd, "@city", DbType.String, entity.city);
            DbAccess.AddInParameter(cmd, "@birthday_year", DbType.Int32, entity.birthday_year);
            DbAccess.AddInParameter(cmd, "@birthday_month", DbType.Int32, entity.birthday_month);
            DbAccess.AddInParameter(cmd, "@birthday_day", DbType.Int32, entity.birthday_day);
            DbAccess.AddInParameter(cmd, "@duty", DbType.Int32, entity.duty);
            DbAccess.AddInParameter(cmd, "@label", DbType.String, entity.label);
            DbAccess.AddInParameter(cmd, "@introduce", DbType.String, entity.introduce);
            DbAccess.AddInParameter(cmd, "@address", DbType.String, entity.address);
            DbAccess.AddInParameter(cmd, "@tel", DbType.String, entity.tel);
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;

            bool ret = DbAccess.ExecuteNonQuery(cmd) > 0;
            newid = 0;
            if (ret)
            {
                newid = Convert.ToInt32(cmd.Parameters["@id"].Value);
            }
            return ret;
        }

        public int BatchDelete(string ids)
        {
            string sql = "delete from member where id in(" + ids + ")";
            return DbAccess.ExecuteNonQuery(sql);
        }

    }
}
